﻿
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using EIS.Obj;


namespace EIS.DatabaseHelper
{
    public class DBHandler:IDisposable 
    {
        private SQLiteConnection _conn = new SQLiteConnection();

        public DBHandler()
        {
            var directoryName = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
            if (directoryName != null)
                _conn.ConnectionString = ("Data Source=" +
                                          directoryName.Remove(
                                              0, 6) + (@"\data.db;Version=3"));
            _conn.Open();

        }
        public void Dispose()
        {
            if ( _conn != null )
                _conn.Close();
        }

        public SQLiteConnection GetDBConnection()
        {
            return _conn;
        }

        public string[] GetCurrentTableColumns(string dbTableName)
        {
            
             var dt = _conn.GetSchema(SQLiteMetaDataCollectionNames.Columns, new string[] { "main", "Table_Name" });

            


            return (from DataRow dr in dt.Rows where dr["Table_Name"].ToString() == dbTableName select (string) dr.ItemArray[3]).ToArray();
        }

        public bool TableExist(string dbTableName)
        {
   
            var sql = @"SELECT * FROM sqlite_master WHERE NAME =" + "'" + dbTableName +
                      "'";
            var sc = new SQLiteCommand(sql, _conn);
            var da = new SQLiteDataAdapter(sc) {SelectCommand = sc};
            var ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0].Rows.Count != 0;
        }

        public bool CreateTable(string functionID)
        {
            if (TableExist(EISConstant.GetFunctionDataTableName(functionID))) return true;
            var sql = @"CREATE TABLE " + "\"" + EISConstant.GetFunctionDataTableName(functionID) + "\"" +
                      @"( " + EISConstant.EISJavaServerMainID + " int, " + EISConstant.EISTimeStamp + " BIGINT, " + EISConstant.EISItemUnread + " char(1)," + EISConstant.EISItemStatus + " char(10)," + " PRIMARY KEY (" + EISConstant.EISJavaServerMainID + "))";
            //
            var sc = new SQLiteCommand(sql, _conn);
            sc.ExecuteNonQuery();

            sql = @"CREATE INDEX idx_timestamp" + "_" + functionID + "  on " + EISConstant.GetFunctionDataTableName(functionID) + @" ( " + EISConstant.EISTimeStamp + " )";
            sc.CommandText = sql;
            sc.ExecuteNonQuery();

            sql = @"CREATE INDEX idx_unread" + "_" + functionID + "  on " + EISConstant.GetFunctionDataTableName(functionID) + @" ( " + EISConstant.EISItemUnread + " )";
            sc.CommandText = sql;
            sc.ExecuteNonQuery();

          
            return true;
        }

        public bool CreateFunctionReadUnreadIndexTable(string functionID)
        {
  
            string table = EISConstant.GetReadUnreadTableName(functionID);
            if (TableExist(table)) return true;
            var sql = @"CREATE TABLE " + "\"" + table + "\"" +
                      @"(
ID int,
Read bool,
PRIMARY KEY (ID)

)";

            var sc = new SQLiteCommand(sql, _conn);
            sc.ExecuteNonQuery();

            sql = @"CREATE INDEX idx_read" + "_" + functionID + " on " + table + @" ( Read )";
            sc.CommandText = sql;
            sc.ExecuteNonQuery();

            sql = @"CREATE UNIQUE INDEX idx_all" + "_" + functionID + " on " + table + @" ( ID, Read )";
            sc.CommandText = sql;
            sc.ExecuteNonQuery();


            return true;
        }


        public bool CreateFunctionSchemaTable(string functionID)
        {
 
            string table = EISConstant.GetSchemaTableName(functionID);
            if (TableExist(table)) return true;
            var sql = @"CREATE TABLE " + "\"" + table + "\"" +
                      @"(
FieldName varchar(30),
DispalyName varchar(20),

PRIMARY KEY (FieldName)

)";

            var sc = new SQLiteCommand(sql, _conn);
            sc.ExecuteNonQuery();



            return true;
        }

        public bool CreateFunctionLayoutTable(string functionID)
        {
            string table = EISConstant.GetLayoutTableName(functionID);
            if (TableExist(table)) return true;
            var sql = @"CREATE TABLE " + "\"" + table + "\"" +
                      @"(
FieldName varchar(30),
ColumPosition int,
PRIMARY KEY (FieldName)
)";

            var sc = new SQLiteCommand(sql, _conn);
            sc.ExecuteNonQuery();

  

            return true;
        }



        public bool AddColumnToTable(string dbTableName, List<FunctionDataTableColumn> columns)
        {

            foreach (var column in columns)
            {
                var sql = @"ALTER TABLE " + "\"" + dbTableName + "\"" + " ADD " + column.FieldName + " " + column.DataType + "(" + column.Length + ")";
                var sc = new SQLiteCommand(sql, _conn);
                sc.ExecuteNonQuery();

            }

            

            return true;
        }

        public DataSet SelectSimple(string tableName)
        {
            return SelectSimple(tableName, null, null, null);
        }

        public long GetMaxTimeStamp(string tableName)
        {
            try
            {
                var con = GetDBConnection();

                var sql = "select max(" +EISConstant.EISTimeStamp +" ) from " + tableName;

                var da = new SQLiteDataAdapter(sql, con);

                var emptyDataSet = new DataSet();
                da.Fill(emptyDataSet);

                return Int64.Parse(emptyDataSet.Tables[0].Rows[0][0].ToString());
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public DataSet GetEmptyDataSet(string tableName)
        {
            try
            {
                var con = GetDBConnection();

                var sql = "select * from " + tableName + " where 1 = 2";

                var da = new SQLiteDataAdapter(sql, con);

                var emptyDataSet = new DataSet();
                da.Fill(emptyDataSet);

              
                return emptyDataSet;
            }
            catch (Exception)
            {
                return null;
            }
        }
        public DataSet SelectSimple(string tableName, string[] requestedFields,
                                           KeyValuePair<string, string>[] parameters,
                                           KeyValuePair<string, string>[] operators)
        {
            var con = GetDBConnection();

           
            var sqlString = "SELECT";
            if (requestedFields == null)
                sqlString = sqlString + " * ";
            else
            {
                foreach (var field in requestedFields)
                {
                    sqlString = sqlString + " ";
                    sqlString = sqlString + field;
                }
            }


            sqlString = sqlString + " " + "FROM" + " " + tableName + " ";

            if (parameters != null)
            {
                sqlString = sqlString + "WHERE";

                foreach (var kvp in parameters)
                {
                    sqlString = sqlString + " " + kvp.Key + " " + FindValueByKey(operators, kvp.Key) + " @" + kvp.Key +
                                " AND";
                }
                //Remove last AND. 
                string remove = sqlString.Remove(sqlString.Length - 4, 4);
                sqlString = remove;

            }
            var sc = new SQLiteCommand(sqlString, con);
            if (parameters != null)
            {
                foreach (var kvp in parameters)
                {
                    var sqlPar = new SQLiteParameter();
                    sqlPar.ParameterName = "@" + kvp.Key;
                    sqlPar.Value = kvp.Value;
                    sc.Parameters.Add(sqlPar);
                }
            }
            var da = new SQLiteDataAdapter(sc) {SelectCommand = sc};
            var ds = new DataSet();
            da.Fill(ds);
     
            return ds;
        }

        public DataSet SelectSimpleBetweenTimStmp(string tableName, long tickFrom, long tickTo)
        {
            var con = GetDBConnection();
           

                var sqlString = "SELECT * ";


                sqlString = sqlString + " " + "FROM" + " " + tableName + " WHERE " + EISConstant.EISTimeStamp + " > " +
                            tickFrom + " AND " + EISConstant.EISTimeStamp + " < " + tickTo;

                SQLiteCommand sc = new SQLiteCommand(sqlString, con);
                var da = new SQLiteDataAdapter(sc) {SelectCommand = sc};
                var ds = new DataSet();
                
                da.Fill(ds);

                return ds;
         
        }

        public DataSet SelectSimpleFromTimStmp(string tableName, long tickFrom)
        {
            return SelectSimpleBetweenTimStmp(tableName, tickFrom, Int64.MaxValue);
        }

        private static string FindValueByKey(IEnumerable<KeyValuePair<string, string>> keyValuePairs, string key)
        {

            if (keyValuePairs == null)
                return null;

            return (from pair in keyValuePairs where pair.Key.Equals(key) select pair.Value).FirstOrDefault();
        }

        
        public bool InsertRecords(string tableName, DataTable dt)
        {
            try
            {
                var con = GetDBConnection();

         

                var sql = "select * from " + tableName + " where 1 = 2";

                var da = new SQLiteDataAdapter(sql, con);
                var cmdBuilder = new SQLiteCommandBuilder(da);
                SQLiteTransaction transaction = con.BeginTransaction();
                SQLiteCommand sd = cmdBuilder.GetInsertCommand();
                sd.Transaction = transaction;
                foreach (DataRow dr in dt.Rows)
                {

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        foreach (SQLiteParameter par in sd.Parameters)
                        {
                            if (par.SourceColumn == dt.Columns[i].ColumnName)
                            {
                                par.Value = dr.ItemArray[i];

                                break;
                            }
                        }
                    }

                    var ia = sd.ExecuteNonQuery();
                }
                transaction.Commit();
      
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        public bool UpdateRecords(string tableName, DataTable dt)
        {
            try
            {
                var con = GetDBConnection();

               

                var sql = "DELETE FROM " + tableName + " where ";

                SQLiteTransaction transaction = con.BeginTransaction();
                SQLiteCommand sd = new SQLiteCommand(con);

                foreach (DataRow dr in dt.Rows)
                {
                    var sqlString = sql + dt.Columns[0].ColumnName + " = '" + dr[dt.Columns[0].ColumnName] + "'";
                    sd.CommandText = sqlString;
                    sd.ExecuteNonQuery();
                }

                var sqlSelect = "select * from " + tableName + " where 1 = 2";

                var da = new SQLiteDataAdapter(sqlSelect, con);
                var cmdBuilder = new SQLiteCommandBuilder(da);
        
                SQLiteCommand si = cmdBuilder.GetInsertCommand();
                si.Transaction = transaction;
        
                foreach (DataRow dr in dt.Rows)
                {

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        foreach (SQLiteParameter par in si.Parameters)
                        {
                            if (par.SourceColumn == dt.Columns[i].ColumnName)
                            {
                                par.Value = dr.ItemArray[i];

                                break;
                            }
                        }
                    }

                    si.ExecuteNonQuery();
                }


                transaction.Commit();
                
                return true;
            }
            catch (Exception)
            {
                
                GetDBConnection().Close();
                return false;
            }

        }

        public int UpdateRecordsReturnUnreadNumber(string tableName, DataTable dt)
        {
            try
            {
                var con = GetDBConnection();


                var sql = "DELETE FROM " + tableName + " where ";
                if (dt != null)
                {
                    SQLiteTransaction transaction = con.BeginTransaction();
                    SQLiteCommand sd = new SQLiteCommand(con);


                    foreach (DataRow dr in dt.Rows)
                    {
                        var sqlString = sql + dt.Columns[0].ColumnName + " = '" + dr[dt.Columns[0].ColumnName] + "'";
                        sd.CommandText = sqlString;
                        sd.ExecuteNonQuery();
                    }

                    var sqlSelect = "select * from " + tableName + " where 1 = 2";

                    var da = new SQLiteDataAdapter(sqlSelect, con);
                    var cmdBuilder = new SQLiteCommandBuilder(da);

                    SQLiteCommand si = cmdBuilder.GetInsertCommand();
                    si.Transaction = transaction;

                    foreach (DataRow dr in dt.Rows)
                    {

                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            foreach (SQLiteParameter par in si.Parameters)
                            {
                                if (par.SourceColumn == dt.Columns[i].ColumnName)
                                {
                                    par.Value = dr.ItemArray[i];

                                    break;
                                }
                            }
                        }

                        si.ExecuteNonQuery();
                    }


                    transaction.Commit();

                }
                SQLiteCommand sn = new SQLiteCommand(con);
                sn.CommandText = "select count(*) from " + tableName + " where " + EISConstant.EISItemUnread + " = 'Y'";

                int rowCount = Convert.ToInt32(sn.ExecuteScalar());
                
                return rowCount;
            }
            catch (Exception)
            {

                GetDBConnection().Close();
                return 0;
            }

        }

        public int UpdateMarkAllAsRead(string tableName)
        {
            try
            {
                var con = GetDBConnection();


                var sql = "UPDATE " + tableName + " SET  " + EISConstant.EISItemUnread + " = 'N'";

                SQLiteTransaction transaction = con.BeginTransaction();
                SQLiteCommand sd = new SQLiteCommand(con);

                sd.CommandText = sql;
                
                sd.Transaction = transaction;
                sd.ExecuteNonQuery();
                transaction.Commit();


                SQLiteCommand sn = new SQLiteCommand(con);
                sn.CommandText = "select count(*) from " + tableName + " where " + EISConstant.EISItemUnread + " = 'Y'";

                int rowCount = Convert.ToInt32(sn.ExecuteScalar());

                return rowCount;
            }
            catch (Exception)
            {

                GetDBConnection().Close();
                return 0;
            }

        }
        public bool DeleteRecords(string tableName, DataTable dt)
        {
            try
            {
                var con = GetDBConnection();

           

                var sql = "DELETE FROM " + tableName + " where ";

                SQLiteTransaction transaction = con.BeginTransaction();
                SQLiteCommand sd = new SQLiteCommand(con);

                foreach (DataRow dr in dt.Rows)
                {
                    var sqlString = sql + dt.Columns[0].ColumnName + " = '" + dr[dt.Columns[0].ColumnName] + "'";
                    sd.CommandText = sqlString;
                    sd.ExecuteNonQuery();
                }


                transaction.Commit();
         
                return true;
            }
            catch (Exception)
            {

                GetDBConnection().Close();
                return false;
            }

        }
        public bool DeleteAllRecordsInTable(string tableName)
        {
            try
            {
                var con = GetDBConnection();

            

                var sql = "DELETE FROM " + tableName ;

   
                SQLiteCommand sd = new SQLiteCommand(con);


                sd.CommandText = sql;

                sd.ExecuteNonQuery();
          
                return true;
            }
            catch (Exception)
            {
                GetDBConnection().Close();
                return false;
            }

        }

        public bool CreateFunctionStatusTable(string functionID)
        {
            if (TableExist(EISConstant.GetStatusTableName(functionID))) return true;
            var sql = @"CREATE TABLE " + "\"" + EISConstant.GetStatusTableName(functionID) + "\"" +
                      @"( " + EISConstant.EISJavaServerMainID + " CHAR, " +  " PRIMARY KEY (" + EISConstant.EISJavaServerMainID + "))";
            //
            var sc = new SQLiteCommand(sql, _conn);
            sc.ExecuteNonQuery();
            return true;
        }
    }
}